﻿Imports System.Data.OleDb

Module XL_CTTraTD
    Structure CTTraTD
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngay As Date
        Property Ngay() As Date
            Get
                Return _ngay
            End Get
            Set(ByVal value As Date)
                _ngay = value
            End Set
        End Property
        
        Private _soTien As Integer
        Property SoTien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        
        Private _maTD As Integer
        Property MaTD() As Integer
            Get
                Return _maTD
            End Get
            Set(ByVal value As Integer)
                _maTD = value
            End Set
        End Property
        Private _daThanhToan As Integer
        Property DaThanhToan() As Integer
            Get
                Return _daThanhToan
            End Get
            Set(ByVal value As Integer)
                _daThanhToan = value
            End Set
        End Property
        Private _conLai As Integer
        Property ConLai() As Integer
            Get
                Return _conLai
            End Get
            Set(ByVal value As Integer)
                _conLai = value
            End Set
        End Property
        Private _laiSuat As Double
        Property LaiSuat() As Double
            Get
                Return _laiSuat
            End Get
            Set(ByVal value As Double)
                _laiSuat = value
            End Set
        End Property
        Private _tongNo As Integer
        Property TongNo() As Integer
            Get
                Return _tongNo
            End Get
            Set(ByVal value As Integer)
                _tongNo = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal mathe As Integer) As List(Of CTTraTD)
        Dim ds As New List(Of CTTraTD)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from CTTraTD where MaTD=@MaTD", con)
            cmd.Parameters.AddWithValue("@MaTD", mathe)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As CTTraTD = New CTTraTD()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.SoTien = rd.GetInt32(2)
                p.MaTD = rd.GetInt32(3)
                p.DaThanhToan = rd.GetInt32(4)
                p.ConLai = rd.GetInt32(5)
                p.LaiSuat = rd.GetDouble(6)
                p.TongNo = rd.GetInt32(7)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As CTTraTD
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from CTTraTD where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As CTTraTD = New CTTraTD()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.SoTien = rd.GetInt32(2)
                p.MaTD = rd.GetInt32(3)
                p.DaThanhToan = rd.GetInt32(4)
                p.ConLai = rd.GetInt32(5)
                p.LaiSuat = rd.GetDouble(6)
                p.TongNo = rd.GetInt32(7)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function Them(ByVal p As CTTraTD) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into CTTraTD(Ngay,SoTien,MaTD,DaThanhToan,ConLai,LaiSuat,TongNo) values(@ng,@st,@mt,@tt,@cn,@ls,@tn)", con)
            cmd.Parameters.AddWithValue("@ng", p.Ngay)
            cmd.Parameters.AddWithValue("@st", p.SoTien)
            cmd.Parameters.AddWithValue("@mt", p.MaTD)
            cmd.Parameters.AddWithValue("@tt", p.DaThanhToan)
            cmd.Parameters.AddWithValue("@cn", p.ConLai)
            cmd.Parameters.AddWithValue("@ls", p.LaiSuat)
            cmd.Parameters.AddWithValue("@tn", p.TongNo)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()

            If kq <> 0 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select sum(TongNo) from CTTraTD where MaTD=@MaTD", con)
                cmd1.Parameters.AddWithValue("@MaTD", p.MaTD)
                Dim kq1 = cmd1.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TheTinDung set SoTien=@SoTien where ID = @ID", con)
                cmd2.Parameters.AddWithValue("@SoTien", kq1)
                cmd2.Parameters.AddWithValue("@ID", p.MaTD)
                Dim kq2 = cmd2.ExecuteNonQuery()
            End If

            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As CTTraTD) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from CTTraTD where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()

            If kq <> 0 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select sum(TongNo) from CTTraTD where MaTD=@MaTD", con)
                cmd1.Parameters.AddWithValue("@MaTD", p.MaTD)
                Dim kq1 = cmd1.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TheTinDung set SoTien=@SoTien where ID = @ID", con)
                cmd2.Parameters.AddWithValue("@SoTien", kq1)
                cmd2.Parameters.AddWithValue("@ID", p.MaTD)
                Dim kq2 = cmd2.ExecuteNonQuery()
            End If

            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As CTTraTD) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update CTTraTD set Ngay=@Ngay,SoTien=@SoTien, MaTD=@MaTD, DaThanhToan=@DaThanhToan, ConLai=@ConLai,TongNo=@tn where ID = @ID", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@SoTien", p.SoTien)
            cmd.Parameters.AddWithValue("@MaTD", p.MaTD)
            cmd.Parameters.AddWithValue("@DaThanhToan", p.DaThanhToan)
            cmd.Parameters.AddWithValue("@ConLai", p.ConLai)
            cmd.Parameters.AddWithValue("@tn", p.TongNo)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            If kq <> 0 Then
                Dim cmd1 As OleDbCommand = New OleDbCommand("select sum(TongNo) from CTTraTD where MaTD=@MaTD", con)
                cmd1.Parameters.AddWithValue("@MaTD", p.MaTD)
                Dim kq1 = cmd1.ExecuteScalar()

                Dim cmd2 As OleDbCommand = New OleDbCommand("update TheTinDung set SoTien=@SoTien where ID = @ID", con)
                cmd2.Parameters.AddWithValue("@SoTien", kq1)
                cmd2.Parameters.AddWithValue("@ID", p.MaTD)
                Dim kq2 = cmd2.ExecuteNonQuery()
            End If
            

            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function No(ByVal the As Integer) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("select sum(TongNo) from CTTraTD where MaTD=@MaTD", con)
            cmd.Parameters.AddWithValue("@MaTD", the)
            Dim kq = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function TinhTongNo(ByVal dst As List(Of CTTraTD)) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim songay As Integer = 0
            Dim tn As Integer = 0
            Dim mt As Integer = 0

            Dim kq As Integer = 0
            For Each p As CTTraTD In dst
                mt = p.MaTD
                If p.ConLai <> 0 Then
                    songay = DateDiff(DateInterval.Day, p.Ngay, Now.Date)
                    If songay > 15 Then
                        p.TongNo = p.ConLai + p.ConLai * p.LaiSuat / 100 * (songay - 15) / 30
                    Else
                        p.TongNo = p.ConLai
                    End If

                    tn = tn + p.TongNo

                    Dim cmd As OleDbCommand = New OleDbCommand("update CTTraTD set TongNo=@tn where ID=@id", con)
                    cmd.Parameters.AddWithValue("@tn", p.TongNo)
                    cmd.Parameters.AddWithValue("@id", p.ID)
                    kq = kq + cmd.ExecuteNonQuery()
                End If
            Next

            Dim cmd2 As OleDbCommand = New OleDbCommand("update TheTinDung set SoTien=@tn where ID=@id", con)
            cmd2.Parameters.AddWithValue("@tn", tn)
            cmd2.Parameters.AddWithValue("@id", mt)
            Dim kq2 = cmd2.ExecuteNonQuery()

            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function
End Module
